package com.template.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.template.DBUtils;
import com.template.TableBean;

public class Utils {

	
	public static String getTableComments(String tableName) {
		// 获得数据库连接
 Connection conn = null;
ResultSet rs = null;
 PreparedStatement ptmt = null;
 String tableComment = "";
try
  {
    conn = DBUtils.getConnection();

	String sql = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME='"+tableName+"' ";

     // 通过数据库的连接操作数据库，实现增删改查
   ptmt = conn.prepareStatement(sql.toString());

     rs = ptmt.executeQuery();


  while (rs.next())
        {
        tableComment =  rs.getString("TABLE_COMMENT"); 
      }
     }
   catch (SQLException e)
    {
      e.printStackTrace();
    }
  finally
  {

  try
 {
      if (rs != null)
      {
	             rs.close();
		       }
	       if (ptmt != null)
	        {
		             ptmt.close();
		      }
     }
		      catch (SQLException e)
		   {
		         e.printStackTrace();
		       }
	     }
		
    return  tableComment;
	}
	
	
	public static void parseColumns(String schemaName,String tableName,List<TableBean> columnList,List<TableBean> priList, List<TableBean> forList,List<TableBean> noPriList) {
		
		// 获得数据库连接
 Connection conn = null;
ResultSet rs = null;
 PreparedStatement ptmt = null;

try
  {
    conn = DBUtils.getConnection();

	String sql = "SELECT COLUMN_NAME," + //列名
			"ORDINAL_POSITION," + //列位�?1,2,3
			"IS_NULLABLE,"+ //YES,NO
			"DATA_TYPE," + //数据类型 int,varchar,date
			"COLUMN_DEFAULT," + //默认�?
			"CHARACTER_MAXIMUM_LENGTH," + //�?大字符长度，针对VARCHAR
			"COLUMN_COMMENT," + //备注
			"COLUMN_KEY FROM information_schema.COLUMNS WHERE table_schema='"+schemaName+"' and table_name = '"+tableName+"' ORDER BY ORDINAL_POSITION ASC "; //PRI=表示主键 MUL=外键

     // 通过数据库的连接操作数据库，实现增删改查
   ptmt = conn.prepareStatement(sql.toString());

     rs = ptmt.executeQuery();


  while (rs.next())
        {
      
		TableBean bean = new TableBean();
		bean.setColumnName(rs.getString("COLUMN_NAME"));
		bean.setIsNullable(rs.getString("IS_NULLABLE"));
		bean.setOrdinalPosition(rs.getString("ORDINAL_POSITION"));
		bean.setDataType(rs.getString("DATA_TYPE"));
		bean.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
		bean.setCharacterMaximumLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
		bean.setColumnComment(rs.getString("COLUMN_COMMENT"));
		bean.setColumnKey(rs.getString("COLUMN_KEY"));
		columnList.add(bean);
		if("PRI".equals(bean.getColumnKey())) {
			priList.add(bean);
		}
		else {
			noPriList.add(bean);
		}
		if("MUL".equals(bean.getColumnKey())) {
			forList.add(bean);
		}     
      }
      }
   catch (SQLException e)
    {
      e.printStackTrace();
    }
  finally
  {

  try
 {
      if (rs != null)
      {
	             rs.close();
		       }
	       if (ptmt != null)
	        {
		             ptmt.close();
		      }
     }
		      catch (SQLException e)
		   {
		         e.printStackTrace();
		       }
	     }
		
		
	}
	
}
